SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
--select * from tb_oa_approval
alter trigger tb_oa_approval_au
on dbo.tb_oa_approval for update
as
begin
  set nocount on
  declare @id int,
    @nstatus int,
    @ostatus int,
    @autocheckbill int,
    @automsg int,
    @btype int,
    @bid int,
    @msgid int,
    @tablename varchar(100),
    @msgName nvarchar(60),@msgContent nvarchar(2000),
    @sql varchar(2000)
  declare cur_app cursor local forward_only static optimistic for
    select i.id,i.status,d.status,i.autocheckbill,i.automsg,i.btype,i.bid,i.name,i.content
      from inserted i,deleted d where i.id=d.id
  open cur_app
  fetch next from cur_app into @id,@nstatus,@ostatus,@autocheckbill,@automsg,@btype,@bid,@msgname,@msgContent
  while @@fetch_status = 0
  begin    
    if @ostatus<>5 and @nstatus=5
    begin
      if @autocheckbill=1
      begin
        select @tablename=btable from ts_billset where bid=@btype      
        set @sql='update '+@tablename+' set bstate=1 where bid='+CAST(@bid as varchar)
        exec sp_executesql @sql
      end  
      if @automsg=1  
      begin
        select @msgid=MAX(msg_id) from ts_messages 
        select @msgid=ISNULL(@msgid,0)+1
        insert into ts_messages (MSG_ID,MSG_Text,MSG_Dispose,MSG_FromUserID,MSG_ToUserID
          ,CREATE_Time,MSG_Type,MSG_BType,MSG_Bid,MSG_MsgTypeID) 
          values (@msgid,@msgName,@msgContent,0,null
          ,GETDATE(),1,33910,@id,-1)
      end
    end
    else if @ostatus=5 and @nstatus<>5
    begin
      if @autocheckbill=1
      begin
        select @tablename=btable from ts_billset where bid=@btype      
        set @sql='update '+@tablename+' set bstate=0 where bid='+CAST(@bid as varchar)
        exec sp_executesql @sql
      end  
      if @automsg=1  
      begin
        delete ts_messages where MSG_BType=33910 and MSG_Bid=@id
      end    
    end
    fetch next from cur_app into @id,@nstatus,@ostatus,@autocheckbill,@automsg,@btype,@bid,@msgname,@msgContent
  end
  close cur_app
  deallocate cur_app
end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

